package com.shengruan.admin.jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.shengruan.admin.vo.User;
import com.shengruan.admin.vo.UserMessage;
import com.shengruan.common.Constants;
import com.shengruan.framework.jdbc.BaseJdbc;
import com.shengruan.framework.jdbc.BeanHandler;
import com.shengruan.framework.jdbc.BeanListHandler;
import com.shengruan.framework.jdbc.DBUtils;
import com.shengruan.index.dto.RegistrationDTO;

public class UserJdbc extends BaseJdbc {

    protected static Log log = LogFactory.getLog(UserJdbc.class);

    //添加用户
    public static final String USER_INSERT = "INSERT INTO users (userName, PASSWORD, realName, sex, email, mobile, creationTime ,loginAttempts, logins) VALUES( ?, ?, ?, ?, ?, ?, ?, ?, ?)";

    //修改密码
    public static final String USER_CHANGE_PASSWORD = "update users set password = ? where id = ?";

    //修改email
    public static final String USER_CHANGE_EMAIL = "update users set email = ? where id = ?";


    private static final String USER_FINDBYID = "select * from users where id = ?";

    public static final String USER_GETBYUSERNAME = "select * from users where userName = ?";

    public static final String USER_GETBYMOBILE = "select * from users where mobile = ?";

    public static final String USER_LAST_INSERT_ID = "SELECT LAST_INSERT_ID() as LAST_INSERT_ID";


    public static final String USERACCOUNT_INSERT = "INSERT INTO user_account (userId, amount, rechargeAmount, withdrawAmount, BiddingCount, creationTime)VALUES(?, ?, ?, ?, ?, ?)";

    /**
     * 保存用户
     *
     * @param user
     */
    public void save(User user) {
        Connection conn = this.getConnection();

        Object[] pram = {user.getUserName(), user.getPassword(), user.getRealName(), user.getSex(), user.getEmail(), user.getMobile(),
                user.getCreationTime(), user.getLoginIP(), user.getLoginTime(), user.getZip(), user.getCountry(), user.getCity(), user.getCounty(),
                user.getOptions(), user.getAddress()};

        try {
            this.execute(conn, USER_INSERT, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }


    /**
     * 用户注册
     *
     * @param user         用户对象
     * @param questionList 问题和回答
     * @param invitePeople 邀请人名称
     */
    public void registration(User user, RegistrationDTO registrationDTO) {
        Connection con = this.getConnection();
        PreparedStatement psta = null;
        PreparedStatement pstaLastInsertId = null;
        PreparedStatement questionPsta = null;
        PreparedStatement userAccountPsta = null;
        ResultSet rs = null;
        try {

            //con.setAutoCommit(false);//设置事务为手动控制
            psta = con.prepareStatement(USER_INSERT);

            psta.setString(1, user.getUserName());
            psta.setString(2, user.getPassword());
            psta.setString(3, user.getRealName());
            psta.setString(4, user.getSex());
            psta.setString(5, user.getEmail());
            psta.setString(6, user.getMobile());
            psta.setLong(7, user.getCreationTime());
            psta.setInt(8, user.getLoginAttempts());
            psta.setInt(9, user.getLogins());
            psta.executeUpdate();


            pstaLastInsertId = con.prepareStatement(USER_LAST_INSERT_ID);
            rs = pstaLastInsertId.executeQuery();
            int lastInsertId = 0;
            if (rs.next()) {
                lastInsertId = rs.getInt("LAST_INSERT_ID");
            }

            int question1Id = 0;
            if("-1".equals(registrationDTO.getQuestionId())) {
                QuestionJdbc questionJdbc = new QuestionJdbc();
                //questionJdbc.save(registrationDTO.getQuestionCustomize1(), lastInsertId);
                //question1Id = questionJdbc.getMAXRows(lastInsertId);
            }
            int question2Id = 0;
            if("-1".equals(registrationDTO.getQuestionId2())) {
                QuestionJdbc questionJdbc = new QuestionJdbc();
                //questionJdbc.save(registrationDTO.getQuestionCustomize2(), lastInsertId);
                //question2Id = questionJdbc.getMAXRows(lastInsertId);
            }


            for (int i=0; i<=1; i++) {
                questionPsta = con.prepareStatement("INSERT INTO question_answer (userId, questionId, answer, creationTime)VALUES(?, ?, ?, ?)");
                questionPsta.setInt(1, lastInsertId);

                if(i == 0) {
                    if("-1".equals(registrationDTO.getQuestionId()))
                        questionPsta.setInt(2, question1Id);
                    else
                        questionPsta.setInt(2, Integer.valueOf(registrationDTO.getQuestionId()));

                    questionPsta.setString(3, registrationDTO.getQuestionAnswer());
                }else {

                    if("-1".equals(registrationDTO.getQuestionId2()))
                        questionPsta.setInt(2, question2Id);
                    else
                        questionPsta.setInt(2, Integer.valueOf(registrationDTO.getQuestionId2()));

                    questionPsta.setString(3, registrationDTO.getQuestionAnswer2());
                }

                questionPsta.setString(4, System.currentTimeMillis() + "");
                questionPsta.executeUpdate();
            }

            //创建用户帐号
            userAccountPsta = con.prepareStatement(USERACCOUNT_INSERT);
            userAccountPsta.setInt(1, lastInsertId);
            userAccountPsta.setDouble(2, 0d);
            userAccountPsta.setDouble(3, 0d);
            userAccountPsta.setDouble(4, 0d);
            userAccountPsta.setInt(5, 0);
            userAccountPsta.setLong(6, System.currentTimeMillis());
            userAccountPsta.executeUpdate();


            BidHistoryJdbc bidhistoryJdbc = new BidHistoryJdbc();
            User inviteUser = this.getByUserName(registrationDTO.getInvitePeople());
            if (null != inviteUser) {
                int sum = bidhistoryJdbc.getSumByUserIdAndType(inviteUser.getId(), 2);//2:表示邀请注册
                if (sum <= Constants.USER_INVITEMAXBIDDING) {
                    //第二次赠送2标，1:注册赠标
                    UserAccountJdbc userAccountJdbc = new UserAccountJdbc();
                    userAccountJdbc.presentationBidding(inviteUser.getId(), 2, 2);

                    //系统邀请赠送投标
                    UserMessageJdbc userMessageJdbc = new UserMessageJdbc();
                    UserMessage userMessage = new UserMessage();
                    userMessage.setUserId(inviteUser.getId());
                    userMessage.setTitle("邀请赠送账户2个投标");
                    userMessage.setContent("您邀请的用户:" + user.getUserName() + "，注册成功，系统赠送您账户2个投标");
                    userMessage.setStatus(Constants.STATUS_INACTIVE);
                    userMessage.setCreationTime(System.currentTimeMillis());
                    userMessageJdbc.save(userMessage);
                }
            }


            //con.commit();//提交事务
            if (Constants.DEBUG_SQL_IF)
                log.debug(USER_INSERT);
        } catch (SQLException e) {
            /*try {
                con.rollback();//回滚事务
            } catch (SQLException ex) {
                ex.printStackTrace();
            }*/
            e.printStackTrace();
        } finally {
            DBUtils.close(rs);
            DBUtils.close(userAccountPsta);
            DBUtils.close(pstaLastInsertId);
            DBUtils.close(questionPsta);
            DBUtils.close(psta, con);
        }
    }

    /**
     * 修改密码
     */
    public void changePassword(String id, String password) {
        Object[] pram = {password, id};
        Connection conn = this.getConnection();
        try {
            this.execute(conn, USER_CHANGE_PASSWORD, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }

    /**
     * 修改手机号码
     */
    public void changeEmail(String id, String email) {
        Connection conn = this.getConnection();
        Object[] pram = {email, id};
        try {
            this.execute(conn, USER_CHANGE_PASSWORD, pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }

    /**
     * 更新用户信息
     *
     * @param user
     */
    public void update(User user) {
        Connection conn = this.getConnection();

        StringBuilder sql = new StringBuilder("update users set userName = ? , password = ? , realName = ? , sex = ? , email = ? , mobile = ? , creationTime = ? , loginIP = ? , loginTime = ?, zip = ? , country = ?, city = ?, county = ? , options = ? ,address = ?, loginAttempts = ?, logins = ? where id = ?");
        Object[] pram = {user.getUserName(), user.getPassword(), user.getRealName(), user.getSex(), user.getEmail(), user.getMobile(), user.getCreationTime(),
                user.getLoginIP(), user.getLoginTime(), user.getZip(), user.getCountry(), user.getCity(), user.getCounty(), user.getOptions(), user.getAddress(),
                user.getLoginAttempts(), user.getLogins(), user.getId()};

        try {
            this.execute(conn, sql.toString(), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }

    /**
     * 根据ID获得用户信息
     *
     * @param id
     * @return
     */
    public User getById(int id) {
        Connection conn = this.getConnection();
        User user = null;
        try {
            user = this.query(conn, USER_FINDBYID, new BeanHandler<User>(User.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return user;
    }


    /**
     * 根据用户名获得用户
     *
     * @param userName
     * @return
     */
    public User getByUserName(String userName) {
        Connection conn = this.getConnection();
        User user = null;
        Object[] pram = {userName};
        try {
            user = this.query(conn, USER_GETBYUSERNAME, new BeanHandler<User>(User.class), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return user;
    }

    /**
     * 根据mobile获得用户
     *
     * @param mobile
     * @return
     */
    public User getByMobile(String mobile) {
        Connection conn = this.getConnection();
        User user = null;
        Object[] pram = {mobile};
        try {
            user = this.query(conn, USER_GETBYMOBILE, new BeanHandler<User>(User.class), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return user;
    }

    /**
     * 根据mobile获得用户
     *
     * @param mobile
     * @return
     */
    public User getByMobile(String mobile, String id) {
        Connection conn = this.getConnection();
        User user = null;
        StringBuffer sql = new StringBuffer("select * from users where mobile = ? and id!=?");
        Object[] pram = {mobile, id};
        try {
            user = this.query(conn, sql.toString(), new BeanHandler<User>(User.class), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return user;
    }

    /**
     * 根据用户名获得用户
     *
     * @param userName
     * @return
     */
    public User getByUserName(String userName, String id) {
        Connection conn = this.getConnection();
        User user = null;
        StringBuffer sql = new StringBuffer("select * from users where userName = ? and id!=?");
        Object[] pram = {userName, id};
        try {
            user = this.query(conn, sql.toString(), new BeanHandler<User>(User.class), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return user;
    }

    /**
     * 获得简单用户
     */
    public User getSimpleUser(int id) {
        Connection conn = this.getConnection();
        User user = null;
        StringBuffer sql = new StringBuffer("select id,userName from users where id=?");

        try {
            user = this.query(conn, sql.toString(), new BeanHandler<User>(User.class), id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return user;
    }

    /**
     * 根据email获得用户
     *
     * @param email
     * @return
     */
    public User getByEmail(String email) {
        Connection conn = this.getConnection();
        User user = null;
        StringBuffer sql = new StringBuffer("select * from users where email = ?");
        Object[] pram = {email};
        try {
            user = this.query(conn, sql.toString(), new BeanHandler<User>(User.class), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return user;
    }

    /**
     * 根据email获得用户
     *
     * @param email
     * @return
     */
    public User getByEmail(String email, String id) {
        Connection conn = this.getConnection();
        User user = null;
        StringBuffer sql = new StringBuffer("select * from users where email = ?  and id!= ? ");
        Object[] pram = {email, id};
        try {
            user = this.query(conn, sql.toString(), new BeanHandler<User>(User.class), pram);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return user;
    }


    /**
     * 获得用户个数
     *
     * @return
     */
    public int getRows(String userName) {
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("select count(u.id) from users u where u.userName like '%" + StringUtils.defaultString(userName) + "%'");
        int count = 0;
        try {
            count = this.getRows(conn, sql.toString());
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return count;
    }

    /**
     * 获得用户列表
     *
     * @param pageSize
     * @param currentPage
     * @param userName
     * @return
     */
    public List<User> findWithPage(int currentPage, int pageSize, String userName) {
        List<User> userList = new ArrayList<User>();

        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("select * from users u where u.userName like '%" + StringUtils.defaultString(userName) + "%' order by u.creationTime desc limit ?,?");
        try {
            int start = (currentPage - 1) * pageSize;
            userList = this.query(conn, sql.toString(), new BeanListHandler<User>(User.class), start, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return userList;
    }


    /**
     * 根据用户ID删除
     *
     * @param id
     */
    public void deleteById(int id) {
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("delete from users where id = ? ");
        try {
            this.execute(conn, sql.toString(), id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }

    /**
     * 设置自动竞拍状态
     */
    public void setAutoAuctionState(int id, int autoAuctionState) {
        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("update users set autoAuctionState=? where id = ? ");
        try {
            this.execute(conn, sql.toString(), autoAuctionState, id);
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
    }


    /**
     * 获得自动竞标的所有用户
     */
    public List<User> getAutoBidUserList() {
        List<User> userList = new ArrayList<User>();

        Connection conn = this.getConnection();
        StringBuffer sql = new StringBuffer("select id from Users where autoAuctionState=1 ");
        try {

            userList = this.query(conn, sql.toString(), new BeanListHandler<User>(User.class));
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            this.close(conn);
        }
        return userList;
    }


}
